--SELECT * FROM FN_GET_SALES_PERSONS_LIST()
/**
	AUTHOR		: S.SUJAN
	DATE		: 09th SEPT 2005
	DESCRIPTION	: TO GET ALL THE SALES PERSONS DETAILS ASSIGNED FOR THE FORECAST
**/
ALTER FUNCTION FN_GET_SALES_PERSONS_LIST ()
RETURNS @RESULTS TABLE ( SALESPERSON_NAME VARCHAR(50),
			 LEVEL_NAME VARCHAR(100),
			 MANAGER VARCHAR(50),
			 USER_IDS VARCHAR(500) ) 
AS
BEGIN
	DECLARE @SPERSONS_CURSOR CURSOR
	DECLARE @USER_IDS_CURSOR CURSOR
	DECLARE @SPERSON_ID INT
	DECLARE @SALESPEROSN_NAME VARCHAR(50)
	DECLARE @LEVEL_NAME VARCHAR(100)
	DECLARE @MANAGER VARCHAR(50)
	DECLARE @USER_IDS VARCHAR(1000)
	DECLARE @USER_ID VARCHAR(100)

	SET @SPERSONS_CURSOR = CURSOR FOR SELECT DISTINCT SP.SalesPerson_Id, SP.SALESPERSON_NAME, FCL.LEVELNAME, 
  				CASE WHEN MGRID IS  NULL THEN '' ELSE ( SELECT SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = FCSP.MGRID ) END AS MANAGER FROM FCSPERSONREL FCSP, SALES_PERSONS SP, FCLEVEL FCL WHERE FCSP.SPERSONID = SP.SALESPERSON_ID AND FCSP.LEVELORDER = FCL.LEVELORDER AND FCL.LEVELSELECT = 0
	OPEN @SPERSONS_CURSOR
	FETCH NEXT FROM @SPERSONS_CURSOR INTO @SPERSON_ID, @SALESPEROSN_NAME, @LEVEL_NAME, @MANAGER
	
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		SET @USER_IDS_CURSOR = CURSOR FOR SELECT USERID FROM FCSPERSONREL WHERE SPERSONID = @SPERSON_ID
		SET @USER_IDS = ''
		OPEN @USER_IDS_CURSOR
		FETCH NEXT FROM @USER_IDS_CURSOR INTO @USER_ID
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			IF @USER_IDS = ''
			 BEGIN
				SET @USER_IDS = @USER_ID
			 END
			ELSE
			 BEGIN
				SET @USER_IDS = @USER_IDS + ',' + @USER_ID
			 END
			SET @USER_ID = ''
			FETCH NEXT FROM @USER_IDS_CURSOR INTO @USER_ID
		 END
		CLOSE @USER_IDS_CURSOR
		DEALLOCATE @USER_IDS_CURSOR

		INSERT INTO @RESULTS ( SALESPERSON_NAME, LEVEL_NAME, MANAGER, USER_IDS ) VALUES ( @SALESPEROSN_NAME, @LEVEL_NAME, @MANAGER, @USER_IDS )
		SET @SPERSON_ID 	= NULL
		SET @SALESPEROSN_NAME	= NULL
		SET @LEVEL_NAME		= NULL
		SET @MANAGER		= NULL
		SET @USER_IDS		= NULL
		FETCH NEXT FROM @SPERSONS_CURSOR INTO @SPERSON_ID, @SALESPEROSN_NAME, @LEVEL_NAME, @MANAGER
	 END
	
	CLOSE @SPERSONS_CURSOR
	DEALLOCATE @SPERSONS_CURSOR
	
	RETURN
END